package com.eascs.loan.service.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.admin.common.utils.StringUtils;
import com.eascs.afterloan.dao.OaAfterDao;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.loan.dao.O2oLoanDao;
import com.eascs.loan.entity.O2oBankLoanData;
import com.eascs.loan.form.O2oLoanForm;
import com.eascs.loan.service.O2oLoanService;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;

import jodd.util.StringUtil;


@Service
public class O2oLoanServiceImpl implements O2oLoanService {

	@Autowired
	O2oLoanDao loanDao;
	@Autowired
	OaAfterDao oaAfterDao;
	@Override
	public PageResult<Object[]> loanList(O2oLoanForm vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class,
				vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT T.bank_loan_id,T.BILL_CODE,T.LOANER_NAME,CONCAT(LEFT (T.LOANER_CARD, 3),'****',RIGHT (T.LOANER_CARD, 3)) as LOANER_CARD,OL.LOAN_TYPE,T.APPLY_AMOUNT/10000 APPLY_AMOUNT ,TP.ITEM_NAME ");
		sql.append(" ,T.LOAN_AMOUNT/10000 LOAN_AMOUNT ,CONCAT(CAST((T.LOAN_RATE*100)AS DECIMAL(10,4)),'%'), ");
		sql.append(" T.LOAN_TERM,OL.PAYMENT_TIME FROM O2O_BANK_LOAN_DATA T  ");
		sql.append(" INNER JOIN O2O_LOANAPPLY OL ON OL.BILL_CODE = T.BILL_CODE JOIN ");
		sql.append(" (SELECT P.* FROM PUB_DDITEM P INNER JOIN PUB_DATADICTIONARY PD ON P.DD_NO =PD.DD_NO ");
		sql.append(" AND PD.DD_CODE ='BANKTYPEO2O') TP ON T.LOAN_BANK = TP.ITEM_CODE ");
		
		sql.append(" WHERE 1=1 and (OL.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(vo.getXdjlNo())) {
			sql.append(" or OL.operator_no in ("+vo.getXdjlNo()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(OL.ltd)**/ 1=1 ) ");
		}
		
		sql.append(" and T.IS_DEL = 'N' AND OL.LOAN_TYPE IN ('BK','BP','BP_JX','SG','YC','SC') ");
		
		//sql.append(" WHERE /**DATA_PERMISSION(OL.LTD)**/ T.IS_DEL = 'N' AND OL.LOAN_TYPE IN ('BK','BP','BP_JX','SG','YC','SC') ");
		//单据编号
		if (StringUtils.isNotEmpty(vo.getBillCode())) {
			sql.append(" AND T.BILL_CODE like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtil.isEmpty(vo.getLoanerName())) {
			sql.append(" AND T.loaner_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanerName().trim());
		}
		if(StringUtils.isNotEmpty(vo.getLoanBank())){
			sql.append(" AND TP.ITEM_CODE = ?");
			sqlPageQuery.addParam(vo.getLoanBank());
		}
		if(StringUtils.isNotEmpty(vo.getLoanDate1())){
			sql.append(" AND T.LOAN_DATE >= ?");
			SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
			try {
				sqlPageQuery.addParam(s.parse(vo.getLoanDate1()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(StringUtils.isNotEmpty(vo.getLoanDate2())){
			sql.append(" AND T.LOAN_DATE <= ?");
			SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
			try {
				sqlPageQuery.addParam(s.parse(vo.getLoanDate2()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(StringUtils.isNotEmpty(vo.getLoanType())){
			sql.append(" AND OL.LOAN_TYPE = ?");
			sqlPageQuery.addParam(vo.getLoanType());
		}
		sql.append(" ORDER BY T.LOAN_DATE desc ");
		sqlPageQuery.setSumColumnIndexs(new int[]{5,7});
  		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = loanDao.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}
	
	@Override
	public PageResult<Object[]> loanOldList(O2oLoanForm vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class,
				vo.getPageSize(), vo.getOldShowPage());
		StringBuilder sql = new StringBuilder();
		sql.append("select op.eaid,ol.billcode,olr.loanername,olr.idcard,ol.loan_type,ol.loanamount,ol.banktype,");
		sql.append(" op.PAYAMOUNT,ol.LOANRATE,ol.loanterm,op.lastupdatetime,op.paytype");
		sql.append(" from eadb.o2o_pay op  ");
		sql.append(" left join eadb.o2o_loanapply ol on op.LOANAPPLYBILLCODE = ol.billcode  ");
		sql.append(" left join  eadb.o2o_loaner olr on ol.LOANAPPLYNO = olr.loanapplyno ");
		sql.append(" where /**DATA_PERMISSION(ol.LTD)**/ op.isdel='N' and op.BILLSTATUS ='CLO' ");
		//单据编号
		if (StringUtils.isNotEmpty(vo.getBillCode())) {
			sql.append(" AND ol.BILL_CODE = ?");
			sqlPageQuery.addParam(vo.getBillCode());
		}
		if (!StringUtil.isEmpty(vo.getLoanerName())) {
			sql.append(" AND olr.LOANERNAME like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanerName().trim());
		}
		if(StringUtils.isNotEmpty(vo.getLoanBank())){
			sql.append(" AND ol.loan_type = ?");
			sqlPageQuery.addParam(vo.getLoanBank());
		}
		if(StringUtils.isNotEmpty(vo.getLoanDate1())){
			sql.append(" AND op.lastupdatetime >= ?");
			SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
			try {
				sqlPageQuery.addParam(s.parse(vo.getLoanDate1()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(StringUtils.isNotEmpty(vo.getLoanDate2())){
			sql.append(" AND op.lastupdatetime <= ?");
			SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
			try {
				sqlPageQuery.addParam(s.parse(vo.getLoanDate2()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if(StringUtils.isNotEmpty(vo.getLoanType())){
			sql.append(" AND ol.LOAN_TYPE = ?");
			sqlPageQuery.addParam(vo.getLoanType());
		}
		sql.append(" ORDER BY op.submittime ");
		//sqlPageQuery.setSumColumnIndexs(new int[]{4,6});
  		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = oaAfterDao.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}
	
	
	@Override
	public List<Object[]>  getBankData(String  billCode , String banktype){
		List<Object[]> list=null;
		StringBuilder sql = new StringBuilder();
		sql.append("select t.bill_code,t.loaner_card,t.loaner_name,p.item_name,t.apply_amount,t.loan_rate,t.loan_amount,t.loan_term,t.loan_date,t.loan_bank from o2o_bank_loan_data t "
				+ "inner join pub_dditem p on t.loan_bank = p.item_code inner join pub_datadictionary pd on p.dd_no =pd.dd_no and pd.dd_code ='BANKTYPEO2O' where 1=1 and t.is_del = 'N' and t.bill_code = ? ");
		if(!StringUtils.empty(banktype)){
			sql.append(" and pay_type = '" + banktype +"'" );
		}else{
			sql.append(" and ( pay_type is null or pay_type ='' ) ");
		}
		if(!StringUtils.empty(billCode)){
			list = loanDao.findObjectsList(sql.toString(), billCode);
		}
		return list;
	}
	
	
	public List<Object[]>  getEaData(String  billCode , String banktype){
		List<Object[]> list=null;
		StringBuilder sql = new StringBuilder();
		sql.append(" select t.bill_code_ea,t.loaner_card,t.loaner_name,'EA' item_name,t.apply_amount,ol.loanrate " );
		sql.append(" ,t.loan_amount,t.loan_term,t.loan_date,t.loan_bank from o2o_ea_loan_data t inner join o2o_loanapply ol " );
		sql.append(" on ol.bill_code=t.bill_code_ea where 1=1 and t.bill_code_ea = ? " );
		if(!StringUtils.empty(banktype)){
			sql.append(" and pay_type = '" + banktype +"'" );
		}else{
			sql.append(" and ( pay_type is null or pay_type ='' ) ");
		}
		if(!StringUtils.empty(billCode)){
			list = loanDao.findObjectsList(sql.toString(), billCode);
		}
		return list;
	}
	
	@Override
	//老系统数据
	public List<Object[]>  getO2OBankData(String  billCode){
		List<Object[]> list=null;
		StringBuilder sql = new StringBuilder();
		sql.append(" select ol.billcode,olr.idcard,olr.loanername, ");
		sql.append(" (select d.itemname from PUB_DDITEM d where d.DDNO=10340 and d.itemcode= ol.banktype) as bank_type, ");
		sql.append(" ol.loanamount,ol.LOANRATE,op.PAYAMOUNT,ol.loanterm,op.lastupdatetime,add_months(sysdate,ol.loanterm),op.paytype ");
		sql.append(" from eadb.o2o_pay op left join eadb.o2o_loanapply ol on op.LOANAPPLYBILLCODE = ol.billcode  ");
		sql.append(" left join  eadb.o2o_loaner olr on ol.LOANAPPLYNO = olr.loanapplyno   ");
		sql.append(" where  op.isdel='N' and op.BILLSTATUS ='CLO' and ol.billcode = ? ");
		if(!StringUtils.empty(billCode)){
			list = oaAfterDao.findObjectsList(sql.toString(), billCode);
		}
		return list;
	}
	
	@Override
	public List<Object[]> isBillCodeExists(String  billCode,String loanName,String idCard){
		List<Object[]> list=null;
		if(!StringUtils.empty(billCode)){
			list = loanDao.findObjectsList("select t.bill_code,l.document_no,l.user_full_name,p.item_name,t.loana_mount,t.loanrate,t.bank_amount, t.loan_term, t.payment_time,p.item_code,'1' from o2o_loanapply t inner  join o2o_loan_user l on t.loan_id=l.loan_id"
					+ " inner join pub_dditem p on t.bank_loan_name = p.item_code inner join pub_datadictionary pd on p.dd_no =pd.dd_no and pd.dd_code ='BANKTYPEO2O' where t.bill_code = ?", billCode);
		}else if(!StringUtils.empty(idCard)){
			list = loanDao.findObjectsList("select t.bill_code,l.document_no,l.user_full_name,p.item_name,t.loana_mount,t.loanrate, t.bank_amount,t.loan_term,  t.payment_time,p.item_code,'2' from o2o_loanapply t inner  join o2o_loan_user l on t.loan_id=l.loan_id "
					+ " inner join pub_dditem p on t.bank_loan_name = p.item_code inner join pub_datadictionary pd on p.dd_no =pd.dd_no and pd.dd_code ='BANKTYPEO2O' where t.wf_type in('SF','O') and l.document_no = ?", idCard);
		}else if(!StringUtils.empty(loanName)){
			list = loanDao.findObjectsList("select t.bill_code,l.document_no,l.user_full_name,p.item_name,t.loana_mount,t.loanrate, t.bank_amount,t.loan_term, t.payment_time,p.item_code, '3' from o2o_loanapply t inner  join o2o_loan_user l on t.loan_id=l.loan_id "
					+ " inner join pub_dditem p on t.bank_loan_name = p.item_code inner join pub_datadictionary pd on p.dd_no =pd.dd_no and pd.dd_code ='BANKTYPEO2O' where t.wf_type in('SF','O')  and  l.user_full_name = ?", loanName);
		}
		return list;
	}
	
	@Override
	public String isBillCodeExport(String  billCode){
		String key_code = loanDao.findString("select t.bank_loan_id from o2o_bank_loan_data t where t.is_del ='N' and t.bill_code = ?",billCode);
		return key_code==null?"":key_code;
	}
	
	
	
	@Override
	public Map<String,String> saveLoan(O2oLoanForm vo){
		Map<String,String> map=new HashMap<String,String>();
		String flag="";
		O2oBankLoanData  Bank =new O2oBankLoanData();
		Bank.setBillCode(vo.getBillCode());
		Bank.setApplyAmount(vo.getApplyAmount());
		Bank.setLoanBank(vo.getLoanBankCode());
		Bank.setLoanAmount(vo.getLoanAmount());
		Bank.setLoanRate(vo.getLoanRate());
		Bank.setLoanTerm(vo.getLoanTerm());
		Bank.setDataType(vo.getDataType());
		Bank.setLoanerName(vo.getLoanerName());
		Bank.setLoanerCard(vo.getLoanerCard());
		Bank.setIsDel("N");
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		if(vo.getLoanDate()!=null){
			try {
				Bank.setLoanDate(sdf.parse(vo.getLoanDate()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		
		boolean insrtFlag=false;
		String keyCode =isBillCodeExport(vo.getBillCode());
		if(StringUtils.isNotEmpty(keyCode)){//修改
			Date date=new Date();
			Bank.setUpdateTime(date);
			User user=UserHolder.getUser();
			if(user!=null){
				Bank.setUpdatePsn(UserHolder.getUser().getCname());
			}
			Bank.setBankLoanId(keyCode);
			try {
				insrtFlag = loanDao.updateById(Bank)>= 1 ? true : false;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{//新增
			Date date=new Date();
			Bank.setOprateTime(date);
			User user=UserHolder.getUser();
			if(user!=null){
				Bank.setOpratePsn(UserHolder.getUser().getCname());
			}
			try {
				insrtFlag = loanDao.insert(Bank)>= 1 ? true : false;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		 
		if (insrtFlag == true) {
			flag="success";
		} else {
			flag="fail";
		}
		map.put("flag", flag);
		map.put("billCode", Bank.getBillCode());
		return map;
		
	}
	
	@Override
	public O2oBankLoanData findByCode(String billCode){
		String loanapplyNo=loanDao.findString("select t.loanapply_no from o2o_loanapply t where t.bill_code = ?",billCode);
		return loanapplyNo==null?null:loanDao.getById(O2oBankLoanData.class, loanapplyNo);
	}
	
	public O2oBankLoanData findEntity(String id){
		String sql="select * from o2o_bank_loan_data t where t.is_del='N' and t.bank_loan_id=?";
		return loanDao.findEntity(sql, O2oBankLoanData.class, id);
	}
	
	@Override
	public Boolean delLoan(String billCode){
		int flag=loanDao.execute("update o2o_bank_loan_data t set t.is_del ='Y' where t.bill_code =? ", billCode);
		return flag>0?true:false;
	}


	@Override
	public O2oBankLoanData queryBillCode(String billCode) {
		return loanDao.findEntity("select * from o2o_bank_loan_data t where t.is_del ='N' and t.bill_code = ?", O2oBankLoanData.class, billCode);
	}
}
